package idv.arthur.work;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class CreateUser
 */
//@WebServlet(description = "創建使用者", urlPatterns = { "/CreateUser" })
public class CreateUser extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public CreateUser() {
        super();
    }

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		Widgets wid = new Widgets();
		String getUserType = request.getParameter("getUserType");
		String returnResult = "成功!";
		String loginUser = request.getParameter("loginUser");
		String userId = request.getParameter("submitId");
		String pwd = request.getParameter("submitPWD");
		String nickName = request.getParameter("submitNickName");
		String fName = request.getParameter("submitFName");
		String lName = request.getParameter("submitLName");
		String email  = request.getParameter("submitEmail");
		String submitRole  = request.getParameter("submitRole");
		
	    Timestamp currentTime = wid.currentTimeStamp();
	    
		String strSQL = "SELECT userID FROM tms_personnel WHERE userID=?";
		Connection conn = wid.getConn(true);
		PreparedStatement ps = null;
		ResultSet rs = null;
		fName = (fName==null?"":fName);
		lName = (lName==null?"":lName);
		email = (email==null?"":email);
		
		
		submitRole = (submitRole.equalsIgnoreCase("0")?"":submitRole);
		
		
		try {
			/** 辨別是要新增還是更新 **/
			
			//是更新嗎？
			if (getUserType != null && !getUserType.equalsIgnoreCase("")) { 
				strSQL = "UPDATE tms_personnel SET pwd=?,nickName=?,lastName=?,firstName=?,email=? WHERE userID=?";
				ps = conn.prepareStatement(strSQL);
				ps.setString(1, pwd);
				ps.setString(2, nickName);
				ps.setString(3, lName);
				ps.setString(4, fName);
				ps.setString(5, email);
				ps.setString(6, getUserType);
				ps.executeUpdate();
				wid.keepLogUser(getUserType, loginUser, 3, conn, ps);
			} else {
				ps = conn.prepareStatement(strSQL);
				ps.setString(1, userId);
				rs = ps.executeQuery();
				
				if (rs.next()){
					returnResult = "ID重覆了";
				} else {
					
					strSQL = "INSERT INTO activiti.tms_personnel(userID, pwd, nickName, lastName, firstName, email, createdTime) VALUES (?,?,?,?,?,?,?)";
					ps = conn.prepareStatement(strSQL);
					ps.setString(1, userId);
					ps.setString(2, pwd);
					ps.setString(3, nickName);
					ps.setString(4, lName);
					ps.setString(5, fName);
					ps.setString(6, email);
					ps.setTimestamp(7, currentTime);
					ps.execute();
					
					strSQL = "SELECT detailSN FROM tms_authorization_desc WHERE typeSN=6";
					ps = conn.prepareStatement(strSQL);
					rs = ps.executeQuery();
					while (rs.next()) {
						strSQL = "INSERT INTO activiti.tms_authorization_body (typeSN, detailSN, userId,creator, CreatedTime) VALUES (?,?,?,?,?)";
						ps = conn.prepareStatement(strSQL);
						ps.setInt(1, 6);
						ps.setInt(2, rs.getInt("detailSN"));
						ps.setString(3, userId);
						ps.setString(4, loginUser);
						ps.setTimestamp(5, currentTime);
						ps.execute();
					}
					if (!submitRole.equalsIgnoreCase("")) {
						String[] roleArray = submitRole.split(",");
						
						for (int i=0;i<roleArray.length;i++) {
							strSQL = "SELECT t2.typeSN, t1.detailSN FROM activiti.tms_role_auth_mapping t1, tms_authorization_desc t2 WHERE t1.roleID=? AND t1.detailSN = t2.detailSN AND t1.detailSN<>15";
							ps = conn.prepareStatement(strSQL);
							ps.setString(1, roleArray[i]);
							rs = ps.executeQuery();
							
							strSQL ="INSERT INTO tms_roleusermapping (roleID,userID) VALUES (?,?)";
							ps = conn.prepareStatement(strSQL);
							ps.setInt(1, Integer.parseInt(roleArray[i]));
							ps.setString(2, userId);
							ps.execute();	
							
							while (rs.next()){
								strSQL = "INSERT INTO activiti.tms_authorization_body (typeSN, detailSN, userId,creator, CreatedTime) VALUES (?,?,?,?,?)";
								ps = conn.prepareStatement(strSQL);
								ps.setInt(1, rs.getInt("typeSN"));
								ps.setInt(2, rs.getInt("detailSN"));
								ps.setString(3, userId);
								ps.setString(4, loginUser);
								ps.setTimestamp(5, currentTime);
								ps.execute();	
							}
						}
					}
				}
			}
			
			
		} catch (SQLException e) {
			returnResult = wid.keepErrorMes(conn, ps, e, "", true);
		} finally {
			try {
				if (rs!= null) {rs.close();}
				if (ps!= null) {ps.close();}
				if (conn!= null) {conn.close();}
			} catch (SQLException e) {returnResult = e.toString();}
		}
		
		response.setContentType("application/text");
		response.setCharacterEncoding("UTF-8");
		response.getWriter().write(returnResult);
	}
}
